package com.ly.datacenter.jdbc.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;

import com.ly.datacenter.component.pager.pojo.MySQLPagination;
import com.ly.datacenter.component.pager.pojo.Pager;
import com.ly.datacenter.crud.json.CRUDField;
import com.ly.datacenter.database.definition.TableDefinition;
import com.ly.datacenter.database.definition.TableDefinitionContainer;
import com.ly.datacenter.database.pojo.FieldMetadata;
import com.ly.datacenter.database.pojo.TableMetadata;
import com.ly.datacenter.pojo.KeyValue;
import com.ly.datacenter.pojo.KeyValueList;
import com.ly.datacenter.servlet.DataSourceContainer;

/**
 * function:
 * @author watson  email: watson.wu@hotmail.com
 * @date 2009-9-1
 */
@SuppressWarnings("unchecked")
public class DAOUtil {

	/**
	 * 
	 * @param connection
	 * @param table
	 * @return
	 */
	public static TableDefinition getTableDefinition(Connection connection, String table) {
		TableMetadata tableMetaData = getTableMetaData(connection, table);
		return new TableDefinition(getCRUDDefinition(tableMetaData), tableMetaData.getFields());
	}

	/**
	 * function:
	 * @param connection
	 * @param tableName
	 * @return
	 */
	public static TableMetadata getTableMetaData(Connection connection, String tableName){
		TableMetadata tableMetaData = new TableMetadata();
		try {
			ResultSet tables = DatabaseUtil.getTableInfoByTableName(connection, tableName);
			List<String> primaryKeys = DatabaseUtil.getTablePrimaryKeyByTableName(connection, tableName);
			if(tables != null && tables.next()) {
				tableMetaData.setTableCatalog(tables.getString(1));
				tableMetaData.setTableSchema(tables.getString(2));
				tableMetaData.setTableName(tables.getString(3));
				tables = null;

				DatabaseMetaData metaData = connection.getMetaData();
				ResultSet columns = metaData.getColumns(null, null, tableName, null);
				List<FieldMetadata> fields = new ArrayList<FieldMetadata>();
				FieldMetadata fieldMetaData;
				while(null != columns && columns.next()) {
					String columnName = columns.getString("COLUMN_NAME");
					String columnTypeName = columns.getString("TYPE_NAME");
					
					int columnSize = columns.getInt("COLUMN_SIZE");
					boolean nullable = columns.getBoolean("IS_NULLABLE");
					String remarks = columns.getString("REMARKS");
					fieldMetaData = new FieldMetadata();
					fieldMetaData.setColumnName(columnName);
					fieldMetaData.setColumnTypeName(columnTypeName);
					fieldMetaData.setPrecision(columnSize);
					fieldMetaData.setNullable(nullable);
					fieldMetaData.setPrimaried(primaryKeys.contains(columnName));
					//can not get the value from  originality database
//					fieldMetaData.setIndexable(false);
//					fieldMetaData.setSigned(false);
//					fieldMetaData.setSearchable(true);
//					fieldMetaData.setDefaultValue(null);
//					fieldMetaData.setDisplayName(displayName);
					fieldMetaData.setComment(remarks);
					
					//do logic modifer
					if(fieldMetaData.isPrimaried()){
						fieldMetaData.setSearchable(true);
//						fieldMetaData.setNullable(false);
//						fieldMetaData.setEditable(false);
					}
					fields.add(fieldMetaData);

//					Statement statement = connection.createStatement();
//					ResultSet resultSet = statement.executeQuery("select * from users");
//					ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//					int number = resultSetMetaData.getColumnCount();
//					List<FieldMetaData> fields = new ArrayList<FieldMetaData>();
//					FieldMetaData fieldMetaData;
//					for (int i=1; i<=number; i++) {
//						fieldMetaData = new FieldMetaData();
//						String columnNameStr = resultSetMetaData.getColumnName(i);
//						fieldMetaData.setColumnName(columnName);
//						fieldMetaData.setColumnTypeName(resultSetMetaData.getColumnTypeName(i));
//						fieldMetaData.setPrecision(resultSetMetaData.getPrecision(i));
//						fieldMetaData.setNullable(resultSetMetaData.isNullable(i)!=0);
//						fieldMetaData.setSigned(resultSetMetaData.isSigned(i));
//						fieldMetaData.setIndexable(false);
//						fieldMetaData.setPrimaried(primaryKeys.contains(columnName));
//						fieldMetaData.setSearchable(resultSetMetaData.isSearchable(i));
//						//can not get the value from  originality database
////						fieldMetaData.setDefaultValue(null);
////						fieldMetaData.setDisplayName(displayName);
////						fieldMetaData.setComment(null);
//						fields.add(fieldMetaData);
//					}
//					tableMetaData.setFields(fields);
				}
				tableMetaData.setFields(fields);
				
//				ResultSetMetaData resultSetMetaData = tables.getMetaData();
//				int number = resultSetMetaData.getColumnCount();
//				List<FieldMetaData> fields = new ArrayList<FieldMetaData>();
//				FieldMetaData fieldMetaData;
//				for (int i=1; i<=number; i++) {
//					fieldMetaData = new FieldMetaData();
//					String columnName = resultSetMetaData.getColumnName(i);
//					fieldMetaData.setColumnName(columnName);
//					fieldMetaData.setColumnTypeName(resultSetMetaData.getColumnTypeName(i));
//					fieldMetaData.setPrecision(resultSetMetaData.getPrecision(i));
//					fieldMetaData.setNullable(resultSetMetaData.isNullable(i)!=0);
//					fieldMetaData.setSigned(resultSetMetaData.isSigned(i));
//					fieldMetaData.setIndexable(false);
//					fieldMetaData.setPrimaried(primaryKeys.contains(columnName));
//					fieldMetaData.setSearchable(resultSetMetaData.isSearchable(i));
//					//can not get the value from  originality database
////					fieldMetaData.setDefaultValue(null);
////					fieldMetaData.setDisplayName(displayName);
////					fieldMetaData.setComment(null);
//					fields.add(fieldMetaData);
//				}
//				tableMetaData.setFields(fields);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tableMetaData;
	}

	/**
	 * 
	 * function:
	 * @param tableMetaData
	 * @return
	 */
	public static List<CRUDField> getCRUDDefinition(TableMetadata tableMetaData) {
		List<FieldMetadata> fields = tableMetaData.getFields();
		return getCRUDDefinition(fields);
	}

	/**
	 * 
	 * @param fields
	 * @return
	 */
	public static List<CRUDField> getCRUDDefinition(List<FieldMetadata> fields) {
		List<CRUDField> crud = new ArrayList<CRUDField>();
		CRUDField object;
		for(FieldMetadata field:fields) {
//			object = new BaseEditField();
			object = new CRUDField();
			object.setName(field.getColumnName());
//			object.setType(getDatabaseColumnTypeClass(field.getColumnTypeName()));
			object.setType(SQLTypeParserUtil.map2CRUDType(field.getColumnTypeName()));
//			object.setValue(field.getValue());
			Map attrs = new HashMap();
			attrs.put("nullable", field.isNullable());
			attrs.put("editable", field.isEditable());
			if(field.isDisplayable())
				attrs.put("displayable", true);
			if(field.isIndexable())
				attrs.put("indexable", true);
			if(field.isPrimaried())
				attrs.put("pk", true);
			if(field.isSearchable())
				attrs.put("searchable", true);
			if(field.isSigned())
				attrs.put("signed", true);
			if(StringUtils.isNotBlank(field.getComment()))
				attrs.put("comment", field.getComment());
			if(null !=field.getDefaultValue())
				attrs.put("defval", field.getDefaultValue().toString());
			if(StringUtils.isNotBlank(field.getDisplayName()))
				attrs.put("label", field.getDisplayName());
			if(0 != field.getPrecision())
				attrs.put("length", field.getPrecision());
			object.setAttrs(attrs);
			crud.add(object);
		}
		return crud;
	}

	/**
	 * 
	 * @param crudFields
	 * @return
	 */
	public static List<FieldMetadata> getFieldMetadata(List<CRUDField> crudFields) {
		//transform
		List<FieldMetadata> fields = new ArrayList<FieldMetadata>();
		FieldMetadata metadata;
		for(CRUDField field:crudFields) {
			metadata = new FieldMetadata();
			metadata.setColumnName(field.getName());
			metadata.setColumnTypeName(SQLTypeParserUtil.map2SQLType(field.getType()));
			Map attrs = field.getAttrs();
			Object nullable = attrs.get("nullable");
			if(null != nullable)
				metadata.setNullable((Boolean) nullable);
			Object editable = attrs.get("editable");
			if(null != editable)
				metadata.setEditable((Boolean) editable);
			Object displayable = attrs.get("displayable");
			if(null != displayable )
				metadata.setDisplayable((Boolean) displayable);
			Object indexable = attrs.get("indexable");
			if(null != indexable )
				metadata.setIndexable((Boolean) indexable);
			Object primaried = attrs.get("pk");
			if(null != primaried )
				metadata.setPrimaried((Boolean) primaried);
			Object searchable = attrs.get("searchable");
			if(null != searchable )
				metadata.setSearchable((Boolean) searchable);
			Object signed = attrs.get("signed");
			if(null != signed )
				metadata.setSigned((Boolean) signed);
			Object comment = attrs.get("comment");
			if(null != comment )
				metadata.setComment((String) comment);
			Object defaultValue = attrs.get("defval");
			if(null != defaultValue )
				metadata.setDefaultValue(defaultValue);
			Object displayName = attrs.get("label");
			if(null != displayName )
				metadata.setDisplayName((String) displayName);
			Object precision = attrs.get("length");
			if(null != precision )
				metadata.setPrecision((Integer) precision);
			fields.add(metadata);
		}
		return fields;
	}

	/**
	 * function:
	 * @param tableMetaData
	 * @return
	 */
	public static JSONArray transformToJSON(TableMetadata tableMetaData) {
		JSONArray array = new JSONArray();
//		array.put("table", tableMetaData.getTableName());
		array.addAll(getCRUDDefinition(tableMetaData));

		return array;
	}

	/**
	 * function:
	 * @param columnTypeName
	 * @return
	 */
	public static Class getDatabaseColumnTypeClass(String columnTypeName) {
		return SQLTypeParserUtil.map2JavaType(columnTypeName);
	}

	/**
	 * function:
	 * @param conn
	 * @param table
	 * @param definition
	 * @param conditions
	 * @param pagerParameters
	 * @return
	 */
	public static List<KeyValueList> query(Connection conn, String table, TableDefinition definition, Map conditions, Map pagerParameters) {
		try {
//			Statement statement = conn.createStatement();
			StringBuffer sql = new StringBuffer("select ");
			KeyValueList display = definition.getDisplayableNameAndTypeName();
			KeyValueList searchable = definition.getSearchableMap();
			if(null == display || display.isEmpty())
				sql.append(" * ");
			else {
				for(int i=0; i<display.size(); i++) {
					sql.append(display.get(i).getKey()).append(i!=display.size()-1?",":"");
				}
			}
			sql.append(" from ").append(table).append(" where 1=1 ");
			ResultSet resultSet = buildCommonConditions(conn, conditions, sql, searchable, pagerParameters);
			if(resultSet != null) {
//				Object extractData = new SqlRowSetResultSetExtractor().extractData(resultSet);
				List<KeyValueList> results = new ArrayList<KeyValueList>();
				KeyValueList map;
				while(resultSet.next()){
					map = new KeyValueList();
					if(null==display || display.isEmpty()) {
						display = definition.getAllFieldsMapTypeName();
					}
					int i=0;
					for(KeyValue keyvalue:display) {
						String column = keyvalue.getKey();
						map.add(new KeyValue(column, ResultSetParserUtils.getValue(resultSet, ++i, keyvalue.getValue().toString())));
					}
					results.add(map);
				}
				return results;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * function:
	 * @param conn
	 * @param table
	 * @param definition
	 * @param conditions
	 * @param pagerParameters
	 * @return
	 */
	public static List queryForList(Connection conn, String table, TableDefinition definition, Map conditions, Map pagerParameters) {
		return query(conn, table, definition, conditions, pagerParameters);
	}

	/**
	 * 
	 * function:
	 * @param table
	 * @param conditions
	 * @param pagerParamters
	 * @return
	 */
	public static Pager queryPager(String table, Map conditions, Map pagerParamters) {
		Connection conn = DataSourceContainer.getConnection();
		TableDefinition definition = getTableDefinition(table, conn);

		List list = queryForList(conn, table, definition, conditions, pagerParamters);
		int totalSize = queryForCount(conn, table, definition, conditions);
		
		Object object = pagerParamters.get(Pager.PAGE);
		return new Pager(object==null?1:Integer.parseInt(object.toString()), 0, totalSize, list, definition.getPk());
	}

	/**
	 * 
	 * function:
	 * @param table
	 * @param connection
	 * @return
	 */
	private static TableDefinition getTableDefinition(String table, Connection connection) {
		TableDefinition definition = TableDefinitionContainer.get(table);
		if(null == definition)
			definition = DAOUtil.getTableDefinition(connection, table);
		return definition;
	}

	/**
	 * 
	 * function:
	 * @param conn
	 * @param table
	 * @param definition
	 * @param conditions
	 * @return
	 */
	private static int queryForCount(Connection conn, String table,
			TableDefinition definition, Map conditions) {
		try {
			StringBuffer sql = new StringBuffer("select count(*) from ").append(table).append(" where 1=1 ");;
			KeyValueList searchable = definition.getSearchableMap();
			ResultSet resultSet = buildCommonConditions(conn, conditions, sql, searchable, null);
			if(null != resultSet && resultSet.next())
				return resultSet.getInt(1);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 
	 * function:
	 * @param conn
	 * @param conditions
	 * @param sql
	 * @param searchable
	 * @param pagerParameters 
	 * @return
	 * @throws SQLException
	 */
	private static ResultSet buildCommonConditions(Connection conn, Map conditions, StringBuffer sql, KeyValueList searchable, Map pagerParameters)
			throws Exception {
		if(null!=conditions && !conditions.isEmpty() && searchable != null && !searchable.isEmpty()) {
			Iterator iterator = conditions.keySet().iterator();
			while(iterator.hasNext()) {
				Object next = iterator.next();
				if(searchable.contains(next.toString()))
//					sql.append(", ").append(next.toString()).append("=").append(conditions.get(next));
					sql.append("and ").append(next.toString()).append("=?");
			}
		}
		//TODO pagination implement
		if(pagerParameters != null && !pagerParameters.isEmpty()){
			int page = Integer.valueOf(pagerParameters.get(Pager.PAGE).toString());
			int pagesize = Integer.valueOf(pagerParameters.get("pagesize").toString());
			
			String databaseName = conn.getMetaData().getDatabaseProductName();
			if(databaseName.startsWith("MySQL")) {
//				sql=new StringBuffer(MySQLPagination.getPaginationSql(sql.toString(), page, pagesize));
				MySQLPagination.getPaginationSql(sql, page, pagesize);
			} else if(databaseName.startsWith("Oracle")) {
				
			} else if(databaseName.startsWith("Microsoft SQL Server")){
				
			}
		}
		PreparedStatement ps = conn.prepareStatement(sql.toString());
		if(null != searchable && !searchable.isEmpty()) {
			Iterator iterator = conditions.keySet().iterator();
			int i=1;
			while(iterator.hasNext()) {
				Object key = iterator.next();
				if(!searchable.contains(key.toString()))
					continue;
				String columnType = KeyValue.getValueOf(searchable, key.toString());
				StatementCreatorUtils.setParameterValueInternal(ps, i, SQLTypeParserUtil.getSqlTypeValue(columnType), null, null, conditions.get(key));
				i++;
			}
		}
		ResultSet resultSet = ps.executeQuery();
		return resultSet;
	}

	/**
	 * function: delete
	 * @param table
	 * @param pks
	 * @return
	 */
	public static boolean deleteByPk(String table, String[] pks) {
		try {
			Connection conn = DataSourceContainer.getConnection();
			TableDefinition definition = getTableDefinition(table, conn);
			
			StringBuffer sql = new StringBuffer("delete from ").append(table).append(" where ").append(definition.getPk()).append(" in(?");
			for(int i=0;i<pks.length-1;i++) {
				sql.append(",?");
			}
			sql.append(")");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			int i=1;
			for(String id:pks)
				StatementCreatorUtils.setParameterValueInternal(ps, i++, SQLTypeParserUtil.getSqlTypeValue(definition.getPKType()), null, null, id);
			int flag = ps.executeUpdate();
			return flag>=0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 
	 * function:
	 * @param table
	 * @param paramters
	 * @return
	 */
	public static boolean insert(String table, Map paramters) {
		try {
			Connection conn = DataSourceContainer.getConnection();
			TableDefinition definition = getTableDefinition(table, conn);
			
			StringBuffer sql = new StringBuffer("insert into ").append(table).append("(");
			StringBuffer values = new StringBuffer(" values(");
			int i=1;
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				while(iterator.hasNext()) {
					sql.append(iterator.next()).append(i!=paramters.size()?",":"");;
					values.append("?").append(i++!=paramters.size()?",":"");
				}
			}
			sql.append(")").append(values).append(")");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				i=1;
				while(iterator.hasNext()) {
					String column = iterator.next().toString();
					StatementCreatorUtils.setParameterValueInternal(ps, i++, SQLTypeParserUtil.getSqlTypeValue(definition.getColumnTypeName(column)), null, null, paramters.get(column));
				}
			}
			int flag = ps.executeUpdate();
			return flag>0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * function:
	 * @param table
	 * @param paramters
	 * @return
	 */
	public static boolean updateByPK(String table, Map paramters) {
		try {
			Connection conn = DataSourceContainer.getConnection();
			TableDefinition definition = getTableDefinition(table, conn);
			String pk = definition.getPk();
			
			StringBuffer sql = new StringBuffer("update ").append(table).append(" set ");
			int i=1;
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				while(iterator.hasNext()) {
					String column = iterator.next().toString();
					if(column.equalsIgnoreCase(pk)){
						i++;
						continue;
					}
					sql.append(column).append("=?").append(i++!=paramters.size()?",":"");;
				}
			}
			sql.append(" where ").append(pk).append("=?");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				i=1;
				while(iterator.hasNext()) {
					String column = iterator.next().toString();
					if(column.equalsIgnoreCase(pk))
						continue;
					StatementCreatorUtils.setParameterValueInternal(ps, i++, SQLTypeParserUtil.getSqlTypeValue(definition.getColumnTypeName(column)), null, null, paramters.get(column));
				}
				StatementCreatorUtils.setParameterValueInternal(ps, i, SQLTypeParserUtil.getSqlTypeValue(definition.getColumnTypeName(pk)), null, null, paramters.get(pk));
			}
			int flag = ps.executeUpdate();
			return flag>0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * function:
	 * @param table
	 * @param paramters just set fields
	 * @param conditions just where fields
	 * @return
	 */
	public static boolean update(String table, Map paramters, Map conditions) {
		try {
			Connection conn = DataSourceContainer.getConnection();
			TableDefinition definition = getTableDefinition(table, conn);
			
			StringBuffer sql = new StringBuffer("update from ").append(table).append(" set ");
			int i=1;
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				while(iterator.hasNext()) {
					sql.append(iterator.next()).append("=?").append(i++!=paramters.size()?",":"");
				}
			}
			if(conditions!=null && !conditions.isEmpty()) {
				sql.append(" where ");
				i=1;
				Iterator iterator = conditions.keySet().iterator();
				while(iterator.hasNext()) {
					sql.append(iterator.next()).append("=?").append(i++!=paramters.size()?",":"");
				}
			}
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			if(null!=paramters && !paramters.isEmpty()) {
				Iterator iterator = paramters.keySet().iterator();
				i=1;
				while(iterator.hasNext()) {
					String column = iterator.next().toString();
					StatementCreatorUtils.setParameterValueInternal(ps, i++, SQLTypeParserUtil.getSqlTypeValue(definition.getColumnTypeName(column)), null, null, paramters.get(column));
				}
			}
			if(conditions!=null && !conditions.isEmpty()) {
				i=1;
				Iterator iterator = conditions.keySet().iterator();
				while(iterator.hasNext()) {
					String column = iterator.next().toString();
					StatementCreatorUtils.setParameterValueInternal(ps, i++, SQLTypeParserUtil.getSqlTypeValue(definition.getColumnTypeName(column)), null, null, conditions.get(column));
				}
			}
			int flag = ps.executeUpdate(sql.toString());
			return flag>0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

}